package Core

import (
    "fmt"
    "strconv"
    "strings"
)

type SqlMaker struct {
    field                   []string
    table                   []string
    insertOrUpdateField     []string
    insertOrUpdateValue     []string
    insertOrUpdateValueReal []string
    where                   []string
    whereValue              []string
    order                   []string
    page                    string
    pageSize                string
    limit                   string
}

func GetSqlMaker() *SqlMaker {
    return new(SqlMaker)
}

func (i *SqlMaker) InSlice(value string, list []string) bool {
    for _, v := range list {
        if v == value {
            return true
            break
        }
    }
    return false
}

func (i *SqlMaker) String2Int64(theString string) int64 {
    theInt64, _ := strconv.ParseInt(theString, 10, 64)
    return theInt64
}

func (i *SqlMaker) SetField(field string) {
    i.field = append(i.field, field)
}

func (i *SqlMaker) SetTable(table string) {
    i.table = append(i.table, table)
}

func (i *SqlMaker) GetTable() string {
    return strings.Join(i.table, ",")
}

func (i *SqlMaker) Set(field, value string) {
    i.insertOrUpdateField = append(i.insertOrUpdateField, field)
    i.insertOrUpdateValue = append(i.insertOrUpdateValue, "?")
    i.insertOrUpdateValueReal = append(i.insertOrUpdateValueReal, value)
}

func (i *SqlMaker) Where(field, operator, value string) {
    if i.InSlice(operator, []string{"<", "<=", "=", ">=", ">", "<>"}) {
        i.where = append(i.where, fmt.Sprintf("%s %s ?", field, operator))
        i.whereValue = append(i.whereValue, value)
    } else if i.InSlice(operator, []string{"like", "not like"}) {
        i.where = append(i.where, fmt.Sprintf("%s %s '%?%'", field, operator))
        i.whereValue = append(i.whereValue, value)
    } else if i.InSlice(operator, []string{"between and"}) {
        temp := strings.Split(value, ",")
        i.where = append(i.where, fmt.Sprintf("%s between ? and ?", field))
        i.whereValue = append(i.whereValue, temp[0])
        i.whereValue = append(i.whereValue, temp[1])
    } else if i.InSlice(operator, []string{"contain"}) {
        i.where = append(i.where, fmt.Sprintf("FIND_IN_SET(?,%s)>0", field))
        i.whereValue = append(i.whereValue, value)
    } else if i.InSlice(operator, []string{"not contain"}) {
        i.where = append(i.where, fmt.Sprintf("FIND_IN_SET(?,%s)<=0", field))
        i.whereValue = append(i.whereValue, value)
    } else if i.InSlice(operator, []string{"match word"}) {
        i.where = append(i.where, fmt.Sprintf("%s REGEXP '[[:<:]]?[[:>:]]'", field))
        i.whereValue = append(i.whereValue, value)
    } else if i.InSlice(operator, []string{"in", "not in"}) {
        i.where = append(i.where, fmt.Sprintf("%s %s (?)", field, operator))
        i.whereValue = append(i.whereValue, value)
    }
}

func (i *SqlMaker) AndWhere(field, operator, value string) {
    i.WhereExpression("and")
    i.Where(field, operator, value)
}

func (i *SqlMaker) OrWhere(field, operator, value string) {
    i.WhereExpression("or")
    i.Where(field, operator, value)
}

func (i *SqlMaker) WhereExpression(operator string) {
    switch operator {
    case "(":
        i.where = append(i.where, "(")
    case ")":
        i.where = append(i.where, ")")
    case "and":
        i.where = append(i.where, " and ")
    case "or":
        i.where = append(i.where, " or ")
    default:
        break
    }
}

func (i *SqlMaker) WhereExpressions(operators []string) {
    for _, operator := range operators {
        i.WhereExpression(operator)
    }
}

func (i *SqlMaker) SetOrder(field, method string) {
    i.order = append(i.order, fmt.Sprintf("%s %s", field, method))
}

func (i *SqlMaker) SetPage(page string) {
    i.page = page
}

func (i *SqlMaker) GetPage() string {
    return i.page
}

func (i *SqlMaker) SetPageSize(pageSize string) {
    i.pageSize = pageSize
}

func (i *SqlMaker) GetPageSize() string {
    return i.pageSize
}

func (i *SqlMaker) SetLimit() {
    page := i.String2Int64(i.GetPage())
    pageSize := i.String2Int64(i.GetPageSize())
    start := (page - 1) * pageSize
    i.limit = fmt.Sprintf("limit %d,%d", start, pageSize)
}

func (i *SqlMaker) GetWhereSql() string {
    var where string
    if len(i.where) > 0 {
        where = fmt.Sprintf("where %s", strings.Join(i.where, ""))
    }
    return where
}

func (i *SqlMaker) GetSelectSql() (string, []string) {
    var field, table, where, order string
    where = i.GetWhereSql()
    if len(i.order) > 0 {
        order = fmt.Sprintf("order by %s", strings.Join(i.order, ","))
    }
    field = strings.Join(i.field, ",")
    table = i.GetTable()
    sql := fmt.Sprintf("select %s from %s %s %s %s", field, table, where, order, i.limit)
    placeholder := i.whereValue
    return sql, placeholder
}

func (i *SqlMaker) GetCountSql() (string, []string) {
    var table, where string
    where = i.GetWhereSql()
    table = i.GetTable()
    sql := fmt.Sprintf("select count(*) as total from %s %s", table, where)
    placeholder := i.whereValue
    return sql, placeholder
}

func (i *SqlMaker) GetInsertSql() (string, []string) {
    var table, insertOrUpdateField, insertOrUpdateValue string
    table = i.GetTable()
    insertOrUpdateField = strings.Join(i.insertOrUpdateField, ",")
    insertOrUpdateValue = strings.Join(i.insertOrUpdateValue, ",")
    sql := fmt.Sprintf("insert into %s (%s) values(%s)", table, insertOrUpdateField, insertOrUpdateValue)
    placeholder := i.insertOrUpdateValueReal
    return sql, placeholder
}

func (i *SqlMaker) GetUpdateSql() (string, []string) {
    var table, where string
    table = i.GetTable()
    var update []string
    for _, field := range i.insertOrUpdateField {
        update = append(update, fmt.Sprintf("%s=?", field))
    }
    updateString := strings.Join(update, ",")
    where = i.GetWhereSql()
    sql := fmt.Sprintf("update %s set %s %s", table, updateString, where)
    placeholder := append(i.insertOrUpdateValueReal, i.whereValue...)
    return sql, placeholder
}

func (i *SqlMaker) GetDeleteSql() (string, []string) {
    var table, where string
    table = i.GetTable()
    where = i.GetWhereSql()
    sql := fmt.Sprintf("delete from %s %s", table, where)
    placeholder := i.whereValue
    return sql, placeholder
}
